package thelist;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.jsp.JspWriter;
import javax.servlet.jsp.PageContext;

import usr.Usr;

import database.Database;

public class thelistMsg {
	
	
	public static int getOtherMessageCount(Usr usr) {
		if ( usr == null ) return 0;
		
		try {

			Connection conn = Database.getConnection();
			Statement stmt = conn.createStatement();
			
			ResultSet rs = stmt.executeQuery(String.format(
						"select count(*) c " +
						"from msg " +
						"where is_boardcast = 0 && to_id != %d",
						usr.getId()));

			rs.next();
			int count = rs.getInt("c");
			
			stmt.close();
			conn.close();
			
			return count;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}		
	}
	
	public static int getPublicMessageCount(Usr usr) {
		if ( usr == null ) return 0;
		
		try {

			Connection conn = Database.getConnection();
			Statement stmt = conn.createStatement();
			
			ResultSet rs = stmt.executeQuery(String.format(
						"select count(*) c " +
						"from msg " +
						"where is_boardcast = 1",
						usr.getId()));
			
			rs.next();
			
			int count = rs.getInt("c");
			
			stmt.close();
			conn.close();
			
			return count;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
	}
	
	public static int getNeededMessageCount(Usr usr) {
		if ( usr == null ) return 0;
		
		try {

			Connection conn = Database.getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs;
			
			if ( usr.getCredit() == 0 ) {
				rs = stmt.executeQuery(
						"select count(*) c " +
						"from msg");

			} else {
				rs = stmt.executeQuery(String.format(
						"select count(*) c " +
						"from msg " +
						"where to_id = %d or is_boardcast = 1",
						usr.getId()));
			}
			
			rs.next();
			
			int count = rs.getInt("c");
			
			stmt.close();
			conn.close();
			return count;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
	}
	
	
	public static int getPrivateMessageCount(Usr usr) {
		if ( usr == null ) return 0;
		
		try {

			Connection conn = Database.getConnection();
			Statement stmt = conn.createStatement();
			
			ResultSet rs = stmt.executeQuery(String.format(
						"select count(*) c " +
						"from msg " +
						"where to_id = %d",
						usr.getId()));
			rs.next();
			
			int count = rs.getInt("c");
			
			stmt.close();
			conn.close();
			
			return count;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
	}
	
	
	public static void getAdminMsgs(Usr usr, PageContext p) {
		if ( usr == null ) return;
		
		JspWriter out = p.getOut();
		
		try {

			Connection conn = Database.getConnection();
			Statement stmt = conn.createStatement();
			
			
			ResultSet rs = stmt.executeQuery(
						"select msg.id id, usr.login `from`, to_id, title, content, dt, is_boardcast " +
						"from msg, usr " +
						"where msg.from_id = usr.id " +
						"order by dt asc");

			while ( rs.next() ) {
				boolean isBoardcast = rs.getBoolean("is_boardcast");
				Usr usrTo = new Usr();
				if ( !isBoardcast ) {
					usrTo.setId(rs.getInt("to_id"));
					usrTo.getUsr();
				}
						
				out.println(
						"<div class='msg' style='height:190px;'>" + "\n" +
						"	<div style='float:left; margin:15px;'>" + "\n" +
						"		<textarea cols='50' rows='1' wrap='off'" + "\n" +
						"			readonly='readonly' title='title'>" + rs.getString("title") + "</textarea>" + "\n" +
						"		<textarea cols='10' rows='1' wrap='off'" + "\n" +
						"			readonly='readonly' title='time'>" + rs.getDate("dt") + "</textarea>" + "\n" +
						"		<br />" + "\n" +
						"		<textarea cols='30' rows='1' wrap='off'" + "\n" +
						"			readonly='readonly' title='from'>" + rs.getString("from") + "</textarea>"
				);
				
				if ( isBoardcast )
					out.println(
						"		<img src='img/boardcast.png' title='boardcast'/>"
					);
				else
					out.println(
						"		<textarea cols='30' rows='1' wrap='off'" + "\n" +  
						"			readonly='readonly' readonly='readonly' title='to'>" + usrTo.getLogin() + "</textarea>"
					);
							
				out.println(
						"		<br />" + "\n" +
						"		<textarea cols='63' rows='3' swap='virtual' " + "\n" +
						"			readonly='readonly' title='content'>" + rs.getString("content") + "</textarea>" + "\n" +
						"	" + "\n" +
						"	</div>" + "\n" +
						"	<div style='float:right; margin:20px 5px 0 0;'>" + "\n" +
						"		<a href='DeleteMsg?msgid=" + rs.getInt("id") + "'>" + "\n" +
						"		<div class='label task' title='Delete Message'" + "\n" +
						"			style='background-image:url(img/delete_big.png)'>" + "\n" +
						"		</div>" + "\n" +
						"		</a>" + "\n" +
						"	</div>"
				);

				out.println(
						"	<div style='float:right; margin:10px 5px 0 0;'>" + "\n" +
						"		<a href='UpdateMsg?msgid=" + rs.getInt("id") + "'>" + "\n" +
						"		<div class='label task' title='Edit Message'" + "\n" +
						"			style='background-image:url(img/inspect_arrow.png)'>" + "\n" +
						"		</div>" + "\n" +
						"		</a>" + "\n" +
						"	</div>" + "\n" +
						"</div>"
				);
			}
				
			stmt.close();
			conn.close();
			
		} catch (SQLException | IOException e) {
				e.printStackTrace();
		}
	}
	
	public static void getUsrMsgs(Usr usr, PageContext p) {
		if ( usr == null ) return;
		
		JspWriter out = p.getOut();
		
		try {

			Connection conn = Database.getConnection();
			Statement stmt = conn.createStatement();
			
			ResultSet rs = stmt.executeQuery(String.format(
						"select msg.id id, login `from`, title, content, dt, is_boardcast " +
						"from msg, usr " +
						"where msg.from_id = usr.id and (msg.to_id = %d or is_boardcast = 1) " +
						"order by dt asc",
						usr.getId()));
			
			while ( rs.next() ) {
				boolean isBoardcast = rs.getBoolean("is_boardcast");
						
				out.println(
						"<div class='msg'>" + "\n" +
						"	<div style='float:left; margin:15px;'>" + "\n" +
						"		<textarea cols='19' rows='1'" + "\n" +
						"			readonly='readonly' title='title'>" + rs.getString("title") + "</textarea>" + "\n" +
						"		<textarea cols='19' rows='1' " + "\n" +
						"			readonly='readonly' title='from'>" + rs.getString("from") + "</textarea>" + "\n" +
						"		<textarea cols='19' rows='1' " + "\n" +
						"			readonly='readonly' title='time'>" + rs.getDate("dt") + "</textarea>" + "\n" +
						"		<br />" + "\n" +
						"		<textarea cols='63' rows='3' swap='virtual' " + "\n" +
						"			readonly='readonly' title='content'>" + rs.getString("content") + "</textarea>" + "\n" +
						"	" + "\n" +
						"	</div>" + "\n" +
						"	<div style='float:right; margin:5px 5px 0 0;'>" + "\n" +
						"		<a href='DeleteMsg?msgid=" + rs.getInt("id") + "'>" + "\n" +
						"		<div class='label task' title='Delete Message'" + "\n" +
						"			style='background-image:url(img/delete_big.png)'>" + "\n" +
						"		</div>" + "\n" +
						"		</a>" + "\n" +
						"	</div>"
				);
				
				
				
				if ( isBoardcast ) {
					out.println(
						"	<div style='float:right; margin:5px 5px 0 0;'>" + "\n" +
						"		<div class='label task' title='Boardcast'" + "\n" +
						"			style='background-image:url(img/boardcast.png);" + "\n" +
						"					border:none'>" + "\n" +
						"		</div>" + "\n" +
						"	</div>"
					);
				}

				out.println(
						"</div>"
				);
			}
			
			stmt.close();
			conn.close();
			
		} catch (SQLException | IOException e) {
			e.printStackTrace();
		}
	}
	
}
